use r2d2::Pool;
use r2d2_sqlite::SqliteConnectionManager;
use rusqlite::{params, Result};

pub struct SettingsDao {
    pub pool: Pool<SqliteConnectionManager>,
}

impl SettingsDao {
    pub fn new(pool: Pool<SqliteConnectionManager>) -> Self {
        SettingsDao { pool }
    }

    // 创建表
    pub fn create_table(&self) -> Result<(), rusqlite::Error> {
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        conn.execute(
            "CREATE TABLE if not exists settings (
                key TEXT PRIMARY KEY,  
                value TEXT             
            );",
            [],
        )?;
        Ok(())
    }

    pub fn insert(&self, key: &str, value: &str) -> Result<(), rusqlite::Error> {
        let mut conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let tx = conn.transaction()?;
        tx.execute(
            "INSERT INTO settings (key, value) VALUES (?1,?2)",
            params![key, value],
        )?;
        tx.commit()?;
        Ok(())
    }

    pub fn update(&self, key: &str, value: &str) -> Result<(), rusqlite::Error> {
        let mut conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let tx = conn.transaction()?;
        tx.execute(
            "UPDATE settings SET value =?2 WHERE key =?1",
            params![key, value],
        )?;
        tx.commit()?;
        Ok(())
    }

    pub fn get(&self, key: &str) -> Result<Option<String>, rusqlite::Error> {
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let mut stmt = conn.prepare("SELECT value FROM settings WHERE key = ?1")?;
        let value: Option<String> = stmt.query_row(params![key], |row| row.get(0))?;
        Ok(value)
    }

    pub fn exists(&self, key: &str) -> Result<bool, rusqlite::Error> {
        let conn = match self.pool.get() {
            Ok(conn) => conn,
            Err(_e) => {
                return Err(rusqlite::Error::SqliteSingleThreadedMode);
            }
        };
        let mut stmt = conn.prepare("SELECT count(*) FROM settings WHERE key = ?1")?;
        let exists: Option<i64> = stmt.query_row(params![key], |row| row.get(0))?;
        Ok(exists.unwrap_or(0) > 0)
    }
}
